/*
 * CustomerDAO.java
 *
 * Created on February 13, 2007, 1:37 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package Data;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
//test
/**
 *
 * @author Danny Beutler
 */
public class CustomerDAO {
    ///////////////////////////////////////
    ///   Singleton pattern
    
    private static CustomerDAO instance = null;
    
    /** Creates a new instance of CustomerDAO */
    private CustomerDAO() {
    }
    
    public static synchronized CustomerDAO getInstance() {
        if (instance == null) {
            instance = new CustomerDAO();
        }
        return instance;
    }
    
    
    /////////////////////////////////
    ///   CREATE
    
    public CustomerBO create() throws Exception {
        String id = GUID.generate();
        CustomerBO customer = new CustomerBO(id);
        Cache c = Cache.getInstance();
        c.put(customer.getId(), customer);
        return customer;
    }
    public CustomerBO create(String id) throws Exception {
        CustomerBO customer = new CustomerBO(id);
        Cache c = Cache.getInstance();
        c.put(customer.getId(), customer);
        return customer;
    }
    
    
    /////////////////////////////////////
    ///   READ
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     */
    public synchronized CustomerBO read(String id) throws DataException {
        CustomerBO customer;
        Connection conn;
        // check to see if id in the cache
        // if so, return it immediately
        if(Cache.getInstance().containsKey(id)){
            return (CustomerBO) Cache.getInstance().get(id);
        }
        
        
        try {
            
            // retrieve a database connection from the pool
            conn = ConnectionPool.getInstance().get();
            
            // call read with a connection (the other read method in this class)
            customer = CustomerDAO.getInstance().read(id,conn);
            
            // release the connection
            ConnectionPool.getInstance().release(conn);
            // rollback
            conn.rollback();
        }catch (ConnectionPoolException ex) {
            throw new DataException("Unable to communicate with the Database", ex);
            
        }catch (SQLException e) {
            
            
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        return customer;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     */
    public CustomerBO read(String id,Connection conn) throws DataException, SQLException {
        CustomerBO customer;
        // check the cache to see if the customer is in the cache
        // if so, return it immediately
        if(Cache.getInstance().containsKey(id)){
            return (CustomerBO) Cache.getInstance().get(id);
        }
        
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE id=? ");
        pstmt.setString(1,id);
        
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        // if( rs.next()){convert the rs to the customer}
        if(rs.next()){
            try {
                customer = CustomerDAO.getInstance().create(id);
            } catch (Exception ex) {
                throw new DataException("Can't read customer",ex);
            }
            customer.setFirstName(rs.getString("FirstName"));
            customer.setLastName(rs.getString("LastName"));
            customer.setPhone(rs.getString("Phone"));
            customer.setEmail(rs.getString("Email"));
            customer.setAddress(rs.getString("Address"));
            customer.setCity(rs.getString("City"));
            customer.setState(rs.getString("State"));
            customer.setZip(rs.getString("Zip"));
            
            customer.setAlreadyInDB(true);
            Cache.getInstance().put(customer.getId(),customer);
            //customer.setMemberId(rs.getString("MemberID"));
            customer.setMember(MemberDAO.getInstance().getByCustomerID(id,conn));
            //customer.setMemberId(member.getId());
            
            
            //save to the cache
            
            
            // else {handling for no customer with that GUID
        }else{
            throw new DataException("Object was not found in the Database.");
        }
        
        //release the connection back to the pool.
        return customer;
    }
    
//////////////////////////////////
///   UPDATE
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     */
    public synchronized void save(CustomerBO customer) throws DataException {
        
        try {
            // retrieve a database connection from the pool
            Connection conn=ConnectionPool.getInstance().get();
            // call save with a connection (the other save method in this class)
            save(customer, conn);
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }catch (SQLException e) {
            // rollback
            throw new DataException("Could not retrieve record for id=" + customer.getId(), e);
        }catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        
        // release the connection back to the pool
        
    }
    
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(CustomerBO customer, Connection conn) throws SQLException, DataException {
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (customer.isDirty()) {
            if (customer.isAlreadyInDB()) {
                update(customer, conn);
            }else{
                insert(customer, conn);
            }
            // set the dirty flag to false now that we've saved it
            customer.setDirty(false);
        }
        
        // call save(bo, conn) on any subobjects (like CustomerDAO to MembershipDAO)
        if(customer.getMember()!=null){
            MemberDAO.getInstance().save(customer.getMember(),conn);
        }
        
        // touch the cache for the object
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(CustomerBO customer,Connection conn) throws SQLException, DataException {
        // do the update statement
        PreparedStatement pstmt =conn.prepareStatement("UPDATE \"Customer\" SET \"FirstName\" = ?," +
                " \"LastName\" = ?," +
                " \"Phone\" = ?," +
                " \"EMail\" = ?," +
                " \"Address\" = ?," +
                " \"City\" = ?," +
                " \"State\" = ?," +
                " \"Zip\" = ?" +
                " WHERE \"ID\" = ?");
        pstmt.setString(1,customer.getFirstName());
        pstmt.setString(2,customer.getLastName());
        pstmt.setString(3,customer.getPhone());
        pstmt.setString(4,customer.getEmail());
        pstmt.setString(5,customer.getAddress());
        pstmt.setString(6,customer.getCity());
        pstmt.setString(7,customer.getState());
        pstmt.setString(8,customer.getZip());
        pstmt.setString(9,customer.getId());
        
        
        pstmt.executeUpdate();
        System.out.println("CustomerUpdateSuccessful");
    }
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(CustomerBO customer,Connection conn) throws SQLException, DataException {
        // do the insert SQL statement
        PreparedStatement pstmt =conn.prepareStatement("INSERT INTO \"Customer\" VALUES (?,?,?,?,?,?,?,?,?)");
        pstmt.setString(1,customer.getId());
        pstmt.setString(2,customer.getFirstName());
        pstmt.setString(3,customer.getLastName());
        pstmt.setString(4,customer.getPhone());
        pstmt.setString(5,customer.getEmail());
        pstmt.setString(6,customer.getAddress());
        pstmt.setString(7,customer.getCity());
        pstmt.setString(8,customer.getState());
        pstmt.setString(9,customer.getZip());
        
        
        
        pstmt.executeUpdate();
        System.out.println("CustomerInsertSucessful");
        // tell the object that it's now in the db (so we call update next time not insert)
        customer.setAlreadyInDB(true);
    }
    
    
////////////////////////////////////
///   DELETE
    
// We have decided not to support delete because we would like the customers to stay in the database for data archiving.
    
//////////////////////////////
///  SEARCH methods
    
    public List<CustomerBO> getAll() throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" ");
        
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
            throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    
    public List<CustomerBO> getByFirstName(String firstName) throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE " +
                "\"FirstName\" LIKE ?");
        pstmt.setString(1,"%"+firstName+"%");
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
            throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    
    public List<CustomerBO> getByLastName(String lastName) throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE " +
                "\"LastName\" LIKE ?");
        pstmt.setString(1,"%"+lastName+"%");
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
            throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    
    public List<CustomerBO> getByPhoneNumber(String phoneNumber) throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE " +
                "\"Phone\" LIKE ?");
        pstmt.setString(1,"%"+phoneNumber+"%"); 
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
           throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    public List<CustomerBO> getByCustomerNumber(String customerNumber) throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE " +
                "\"ID\" LIKE ?");
        pstmt.setString(1,"%"+customerNumber+"%");
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
            throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    
    public List<CustomerBO> getByEmail(String email) throws DataException, SQLException, Exception {
        // get the connection
        Connection conn=null;
        try {
            conn=ConnectionPool.getInstance().get();
        } catch (ConnectionPoolException e){
            throw new DataException("Could not retrieve Connection", e);
        }
        List<CustomerBO> customerList = new ArrayList<CustomerBO>();
        
        // sql the names, phone, and ids
        PreparedStatement pstmt =conn.prepareStatement("Select * from \"Customer\" WHERE " +
                "\"Email\" = ?");
        pstmt.setString(1,email);
        ResultSet rs = null;
        rs = pstmt.executeQuery();
        // get a rs from an sql SELECT statement
        if(rs.next()){
            do{
                CustomerBO customer = CustomerDAO.getInstance().create(rs.getString("ID"));
                
                customer.setFirstName(rs.getString("FirstName"));
                customer.setLastName(rs.getString("LastName"));
                customer.setPhone(rs.getString("Phone"));
                customer.setEmail(rs.getString("Email"));
                customer.setAddress(rs.getString("Address"));
                customer.setCity(rs.getString("City"));
                customer.setState(rs.getString("State"));
                customer.setZip(rs.getString("Zip"));
                
                customer.setAlreadyInDB(true);
                Cache.getInstance().put(customer.getId(),customer);
                //customer.setMemberId(rs.getString("MemberID"));
                customer.setMember(MemberDAO.getInstance().getByCustomerID(rs.getString("ID"),conn));
                //customer.setMemberId(member.getId());
                
                
                //save to the cache
                customerList.add(customer);
                
                // else {handling for no customer with that GUID
                
                
            }while(rs.next());
            
        }else{
            throw new DataException("Nothing matches the information you entered");
        }
        
        
        // while loop to populate the list from the results
        
        // release my connection
        
        // return the list
        return customerList;
    }
    
}